<?php
/**
 * Server Instance class
 *
 * Use this class twice for connection to 2 different databases in MySQLcompare
 *
 * PHP version 5
 *
 * Copyright (C) 2012 Ewald van Gemert (ewald@vangee.nl)
 * Permission is hereby granted, free of charge, to any person obtaining a copy of this software and 
 * associated documentation files (the "Software"), to deal in the Software without restriction,
 * including without limitation the rights to use, copy, modify, merge, publish, distribute, sublicense,
 * and/or sell copies of the Software, and to permit persons to whom the Software is furnished to do so,
 * subject to the following conditions:
 *
 * The above copyright notice and this permission notice shall be included in all copies or substantial
 * portions of the Software.
 *
 * THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT 
 * LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN 
 * NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, 
 * WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE 
 * SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.
 *
 * @category   Development tool
 * @package    MySQLcompare
 * @author     Ewald van Gemert <ewald@vangee.nl>
 * @license    MIT 3
 * @version    SVN: 
 * @link       http://code.google.com/p/mysqlcompare
 * @since      File available since Release 0.9
 */

define("QUIET", true);  // No error messages or visible output from comapre objects

// ###########################################################################
// Class van een Server Item
// ###########################################################################
class SRVITEM {
	private $mysqli = null;
	protected $filecontent = array();
	private $setting = array();
	private $usedb = "";
	private $linkname = "";
	public $error = "";

	private function host() {		
		return $this->filecontent['mysqlhost'];
	}

	private function user() {
		return $this->filecontent['username'];
	}

	private function pass() {
		return $this->filecontent['password'];
	}
	
	private function excludetables() {
		return (is_array($this->filecontent['excludetables'])) ? $this->filecontent['excludetables'] : array();
	}

	private function fncusedb() {
		if ($this->usedb != "")	$this->mysqli->query("USE `{$this->usedb}`");
	}
	
	private function fnclog($type, $affected, $sql) {
		if (($this->setting['logallquerys'])||($type=="error")) {
			$sql = str_replace(array("\n","\r"), " ", $sql);
			if ($type!="error") $sql = substr($sql, 0, 200);
			$log = date('Ymd H:i:s')." [{$this->linkname}] - $type : ({$affected}) {$this->error} : {$sql}\n";
			$db = ("$this->usedb()"=="") ? ".{$this->usedb()}" : "";
			$file = "{$this->setting['base']}/{$this->setting['logpath']}/{$this->host()}{$db}.log";
			$flags = (file_exists($file)) ? FILE_APPEND : 0;
			file_put_contents ($file, $log, $flags);
		}
	}
	
	private function qry($sql, $resultmode = MYSQLI_STORE_RESULT, $quiet = false) {
		$command = strtoupper(substr(trim($sql), 0, strpos(trim($sql.' '), ' ')));
		if ($command!='USE') $this->fncusedb();
		$ignoreSQL = ($this->setting['log_noexec']&&(!in_array($command, array("SELECT", "USE", "SHOW"))));
		$res = ($ignoreSQL) ? null : $this->mysqli->query("{$sql}", $resultmode);
		if ($this->mysqli->errno != 0) {
			$this->error =  $this->mysqli->error;
			$this->fnclog("error", 0, $sql);
			if (!$quiet) printf("Errormessage: %s -while- %s \n", $this->error, $sql);
		} else {
			$this->error = "";
			$this->fnclog((($ignoreSQL)?"ignored":"success"), $this->mysqli->affected_rows, $sql);
		}
		return $res;
	}

	// ***********************************************************************
	// public functions
	// ***********************************************************************
	public function __construct($arr, $setting, $linkname="-") {
		$this->setting = $setting;	// global site settings
		$this->filecontent = $arr;  // server settings
		$this->linkname = $linkname;
		$this->mysqli = mysqli_init();		
		$this->mysqli->real_connect($this->host(), $this->user(), $this->pass());

		if (mysqli_connect_error()) {
			die('Connect Error (' . mysqli_connect_errno() . ') '
				. mysqli_connect_error() . $this->host() . print_r($this->filecontent));
		}
	}

	// Use these settings to clone this server link
	public function getfilecontent() {
		return $this->filecontent;
	}
	
	// USE
	// Repeat: before every query, execute 'USE `database`'
	function dbuse($db, $repeat=false) {
		$this->usedb = ($repeat==false) ? $db : "";
		$this->qry("USE `{$this->usedb}`");
	}
	
	// Identifies the server
	function identifier() {
		return $this->filecontent['identifier'];
	}

	// Identifies the current database
	function database() {
		return $this->usedb;
	}

	function status() {
		return (isset($this->mysqli)) ? "online" : "offline";
	}

	function dboptions($selected=null) {
		$dboptoins = "";
		$res = $this->qry("SHOW DATABASES");
		if ($res) {
			while ($row = $res->fetch_object()){
				if (!in_array("{$row->Database}", $this->excludetables())) { 
					$dboptoins .= "<option value=\"{$row->Database}\"".(("$selected"=="$row->Database")?" selected":"").">{$row->Database}</option>";
				}
			}
			$res->close();
		}
		return $dboptoins;
	}

	function tblmeta($table, $quiet=false) {
		$arrmeta = array();
		$res = $this->qry("SELECT * FROM `{$table}` LIMIT 1", MYSQLI_STORE_RESULT, $quiet);
		if ($res) {
			while ($meta = $res->fetch_field()) {
				unset($meta->table);
				unset($meta->max_length);
				unset($meta->catalog);
				unset($meta->db);
				$meta->blob        = (($meta->flags & MYSQLI_BLOB_FLAG)>0) ? 1 : 0;
				$meta->unsigned    = (($meta->flags & MYSQLI_UNSIGNED_FLAG)>0) ? 1 : 0;
				$meta->zerofill    = (($meta->flags & MYSQLI_ZEROFILL_FLAG)>0) ? 1 : 0;
				$meta->numeric     = (($meta->flags & MYSQLI_NOT_NULL_FLAG)>0) ? 1 : 0;
				$meta->multiple_key= (($meta->flags & MYSQLI_MULTIPLE_KEY_FLAG)>0) ? 1 : 0;
				$meta->primary_key = (($meta->flags & MYSQLI_PRI_KEY_FLAG)>0) ? 1 : 0;
				$meta->not_null    = (($meta->flags & MYSQLI_NOT_NULL_FLAG)>0) ? 1 : 0;
				$meta->unique_key  = (($meta->flags & MYSQLI_UNIQUE_KEY_FLAG)>0) ? 1 : 0;
				$arrmeta[$meta->name] = $meta;
			}
		}
		return $arrmeta;
	}

	// Create an array of the table data, suitable for comparing to an other table.
	// Create a `row data identifier`
	// Only compare tables that exist in both tables
	function compareTable($table, $arrKeys, $allColumns) {
		$key = array();
		$result = array();
		$res = $this->qry("SELECT * FROM `{$table}`", MYSQLI_STORE_RESULT);
		if ($res) {
			while ($row =  $res->fetch_array(MYSQLI_ASSOC)) {
				$key = array();
				foreach ($arrKeys as $k) $key[] = "{$row[$k]}";
				$rowSelector = array();
				foreach ($arrKeys as $k) $rowSelector[] = array("{$k}" => "{$row[$k]}");
				$rowDataIdentifier = implode('~|k~',$key);
				$rowDataChecksum = "";
				foreach ($row as $nam => $val) {
					$rowDataChecksum .= (in_array($nam, $allColumns)) ? "{$val}" : "";
				}
				$result[$rowDataIdentifier] = array('chk' => md5($rowDataChecksum), 'keys' => $key, 'row' => $row, 'sel' => $rowSelector);
			}
			$res->close();
		}	
		return $result;
	}
	
	function escape($str) {
		return $this->mysqli->real_escape_string($str);
	}

	function num_rows($sql, $quiet=false) {
		$res = $this->qry("{$sql}", MYSQLI_STORE_RESULT, $quiet);
		$row_cnt = 0;
		if ($res) {
			$row_cnt = $res->num_rows;
			$res->close();
		}
		return $row_cnt;
	}
	
	function get_field($sql, $quiet=false) {
		$res = $this->qry("{$sql}", MYSQLI_STORE_RESULT, $quiet);
		$row = ($res) ? $res->fetch_array(MYSQLI_NUM) : array(null);
		if ($res) $res->close();
		return $row[0];
	}
	
	function get_row($sql, $resulttype=MYSQLI_NUM, $quiet=false) {
		$res = $this->qry("{$sql}", MYSQLI_STORE_RESULT, $quiet);
		$row = ($res) ? $res->fetch_array($resulttype) : array();
		if ($res) $res->close();
		return $row;
	}
	
	function get_rows($sql, $resulttype=MYSQLI_NUM, $quiet=false) {
		$result = array();
		$res = $this->qry("{$sql}", MYSQLI_STORE_RESULT, $quiet);
		if ($res) {
			while ($tmp = $res->fetch_array($resulttype)) $result[] = $tmp;
			$res->close();
		}
		return $result;
	}
	
	function exec($sql, $quiet=false) {
		if (is_array($sql)) {
			$this->mysqli->autocommit(false);
			$result = array();
			foreach($sql as $s) {
				$result[] = $this->qry("{$s}", MYSQLI_STORE_RESULT, $quiet);
				if ($this->error != "") {
					$this->mysqli->rollback();
					return false;
				}
			}
			$this->mysqli->commit();
		} else {
			$result = $this->qry("{$sql}", MYSQLI_STORE_RESULT, $quiet);
		}
		return $result;
	}	
	
	function gettables($db, $quiet=false) {
		$arr = array();
		$res = $this->qry("SHOW TABLES FROM `".$db."`", MYSQLI_STORE_RESULT, $quiet);
		if ($res) while ($row = $res->fetch_array()) $arr[] = $row[0];
		if ($res) $res->close();
		return $arr;
	}
	
	function backup($db=null, $file=null) {
		$db = (isset($db)) ? $db : $this->usedb;
		$cmd = sprintf($this->setting['mysqldump'], $this->host(), $this->user(), $this->pass(), $this->setting['dumpoptions'], $db, $file);
		return shell_exec($cmd);
	}

	function restore($db, $file) {
		$cmd = sprintf($this->setting['mysqlrestore'], $this->host(), $this->user(), $this->pass(), $db, $file);
		return shell_exec($cmd);
	}	
}
